<?php
namespace MyApp\Application\Model;

use DinhThi\MVC\Model\MySQLModel;
class HoaDonModel extends MySQLModel{
    public function __construct(){
        parent::connect();
    }

    public function TaoHoaDon($TriGia, $GiaGoc, $listMatHang, $listGiaGoc, $listGiaBan, $listSoLuong, $listThanhTien){
        $sth = $this->DB->prepare("insert into hoadon (trigia, giagoc) values (:trigia, :giagoc)");
        $sth->execute(array(
            ':trigia' => $TriGia,
            ':giagoc' => $GiaGoc
        ));


        $sth = $this->DB->prepare('select LAST_INSERT_ID() as ID');
        $sth->execute();
        $id = $sth->fetch();
        $id = $id['ID'];

        foreach ($listMatHang as $key => $value){
            $sth = $this->DB->prepare("insert into hoadon_chitiet (mahoadon, mamathang, giagoc, giaban, soluong, thanhtien)
                                       values (:mahoadon, :mamathang, :giagoc, :giaban, :soluong, :thanhtien)
            ");

            $sth->execute(
                array(
                    ':mahoadon'     => $id,
                    ':mamathang'    => $value,
                    ':giagoc'       => $listGiaGoc[$key],
                    ':giaban'       => $listGiaBan[$key],
                    ':soluong'      => $listSoLuong[$key],
                    ':thanhtien'    => $listThanhTien[$key]
                )
            );
        }

        return $id;
    }

    public function ThongTinHoaDon($id){
        $sth = $this->DB->prepare("select * from hoadon where id = :id");
        $sth->execute(
            array(
                ':id' => $id
            )
        );

        return $sth->fetch();
    }

    public function ThongTinChiTietHoaDon($id){
        $sth = $this->DB->prepare("select hd.id, hd.trigia, DATE(hd.thoigian) as thoigian,
                                    kh.ten, kh.sodienthoai, kh.diachi, kh.congno as tongno
                                    from hoadon hd
                                    inner join khachhang kh
                                    on kh.id = hd.nguoimua
                                    where hd.id = :id");
        $sth->execute(
            array(
                ':id' => $id
            )
        );

        return $sth->fetch();
    }

    public function ChiTietHoaDon($id){
        $sth = $this->DB->prepare("select mathang.ten, mathang.mamathang, mathang.donvitinh, chitiet.giagoc, chitiet.giaban, chitiet.soluong, chitiet.thanhtien
                                    from hoadon_chitiet chitiet
                                    inner join mathang_data mathang
                                    on chitiet.mamathang = mathang.id
                                    where chitiet.mahoadon = :id");
        $sth->execute(
            array(
                ':id' => $id
            )
        );

        return $sth->fetchAll();
    }

    public function TaoThanhToan($id, $khachhang, $tienconlai, $ghichu){
        $sth = $this->DB->prepare("update hoadon set nguoimua = :khachhang, ghichu = :ghichu where id = :id");
        $sth->execute(
            array(
                ':id'           => $id,
                ':khachhang'    => $khachhang,
                ':ghichu'       => $ghichu
            )
        );

        $newGhiChu = 'Còn nợ lại hóa đơn ' . $id;
        if ($tienconlai == 0){
            $newGhiChu = 'Trả đủ hóa đơn ' . $id;
        }

        $sth = $this->DB->prepare("insert into khachhang_congno (khachhang, sotien, ghichu) values (:khachhang, :sotien, :ghichu)");
        $sth->execute(
            array(
                ':khachhang'    => $khachhang,
                ':sotien'       => $tienconlai,
                ':ghichu'       => $newGhiChu
            )
        );

    }

    public function HuyHoaDon($id){
        $data = $this->ThongTinHoaDon($id);

        if ($data['nguoimua'] == NULL || $data['nguoimua'] == 'NULL'){
            $sth = $this->DB->prepare("DELETE FROM hoadon_chitiet WHERE mahoadon = :id");
            $sth->execute(
                array(
                    ':id' => $id
                )
            );


            $sth = $this->DB->prepare("DELETE FROM hoadon WHERE id = :id");
            $sth->execute(
                array(
                    ':id' => $id
                )
            );
        }

    }

    public function TatCaHoaDon(){

        $sth = $this->DB->prepare("select hd.id, hd.trigia, hd.giagoc, hd.thoigian, hd.ghichu,
                                    kh.ten, kh.diachi, kh.sodienthoai
                                    from hoadon hd
                                    inner join khachhang kh
                                    on hd.nguoimua = kh.id
                                    order by hd.id desc");

        $sth->execute();

        return $sth->fetchAll();
    }


    public function TatCaHoaDonChuaTT(){
        $sth = $this->DB->prepare("select * from hoadon where nguoimua is NULL order by id desc");
        $sth->execute();
        return $sth->fetchAll();
    }



    public function DanhSachHoaDon($begin, $end){
        $sth = $this->DB->prepare("select hd.id, hd.trigia, hd.giagoc, hd.thoigian, hd.ghichu,
                                    kh.ten, kh.diachi, kh.sodienthoai
                                    from hoadon hd
                                    inner join khachhang kh
                                    on hd.nguoimua = kh.id
                                    where UNIX_TIMESTAMP(hd.thoigian) > UNIX_TIMESTAMP(:begin)
                                    and UNIX_TIMESTAMP(hd.thoigian) < UNIX_TIMESTAMP(:end)
                                    and hd.nguoimua is not NULL
                                    order by hd.id desc");
        $sth->execute(array(
            ':begin'    => $begin,
            ':end'      => $end
        ));

        return $sth->fetchAll();
    }

    public function DSHoaDonChuaThanhToan($begin, $end){
        $sth = $this->DB->prepare("SELECT * FROM hoadon WHERE UNIX_TIMESTAMP(thoigian) > UNIX_TIMESTAMP(:begin) and UNIX_TIMESTAMP(thoigian) < UNIX_TIMESTAMP(:end) and nguoimua is NULL order by id desc");
        $sth->execute(array(
            ':begin'    => $begin,
            ':end'      => $end
        ));

        return $sth->fetchAll();
    }

    public function DoanhSoTheoNgay($begin, $end){
        $sth = $this->DB->prepare('SELECT DATE_FORMAT(thoigian, \'%d/%m/%Y\') AS Ngay,
                                    SUM(giagoc) AS GiaGoc, SUM(trigia) AS TriGia
                                    FROM   hoadon
                                    WHERE DATE(thoigian) >= DATE :begin AND DATE(thoigian) <= DATE :end
                                    GROUP BY DATE(thoigian)
                                    ORDER BY Ngay
                                   ');

        $sth->execute(array(
            ':begin'    => $begin,
            ':end'      => $end
        ));

        return $sth->fetchAll();
    }


    public function DoanhSoTheoThang($begin, $end){
        $sth = $this->DB->prepare('SELECT DATE_FORMAT(thoigian, \'%m/%Y\') AS Ngay,
                                    SUM(giagoc) AS GiaGoc, SUM(trigia) AS TriGia
                                    FROM   hoadon
                                    WHERE MONTH(DATE(thoigian)) >= MONTH (DATE :begin) AND MONTH(DATE(thoigian)) <= MONTH (DATE :end)
                                    GROUP BY MONTH (DATE(thoigian))
                                    ORDER BY Ngay
                                   ');

        $sth->execute(array(
            ':begin'    => $begin,
            ':end'      => $end
        ));

        return $sth->fetchAll();
    }
} 